import sys
import os
import logging
import re
import json
from typing import Dict, List, Any, Optional, Tuple
from decimal import Decimal

# 添加项目根路径到 sys.path
sys.path.append(os.path.join(os.path.dirname(os.path.abspath(__file__)), '..', '..'))

# 导入 MySQLUtil
from shared.utils.MySQLUtil import MySQLUtil

# 设置日志
logger = logging.getLogger(__name__)

def get_境内升学率对比分析_和全国对比__定性分析_(
    project_id: int,
    questionnaire_ids: List[int],
    previous_year_1_project_id: Optional[int] = None,
    previous_year_1_questionnaire_ids: Optional[List[int]] = None,
    previous_year_2_project_id: Optional[int] = None,
    previous_year_2_questionnaire_ids: Optional[List[int]] = None,
    product_code: Optional[str] = None,
    project_code: Optional[str] = None,
    region_code: Optional[str] = None
) -> Dict[str, Any]:
    """
    境内升学率对比分析（和全国对比  定性分析） - 指标计算函数
    
    ## 指标说明
    该指标用于计算当前项目及往届项目的境内升学比例，并与全国数据进行对比分析。
    通过比较不同届次的境内升学率变化趋势，评估项目效果和毕业生去向。
    
    ## Args
        project_id (int): 当前项目ID，用于查询项目配置信息
        questionnaire_ids (List[int]): 当前项目问卷ID集合，用于确定数据范围
        previous_year_1_project_id (Optional[int]): 上一届项目ID（如2023届），0表示无数据
        previous_year_1_questionnaire_ids (Optional[List[int]]): 上一届问卷ID集合
        previous_year_2_project_id (Optional[int]): 上两届项目ID（如2022届），0表示无数据
        previous_year_2_questionnaire_ids (Optional[List[int]]): 上两届问卷ID集合
        product_code (Optional[str]): 产品编码，用于路由到特定计算逻辑
        project_code (Optional[str]): 项目编码，用于路由到特定计算逻辑
        region_code (Optional[str]): 区域编码，用于路由到特定计算逻辑
        
    ## 示例
    ### 输入
    ```json
    {
        "project_id": 6768,
        "questionnaire_ids": [12709, 12710],
        "previous_year_1_project_id": 0,
        "previous_year_1_questionnaire_ids": [0],
        "previous_year_2_project_id": 461,
        "previous_year_2_questionnaire_ids": [831, 832]
    }
    ```
    
    ### 输出
    ```json
    {
        "success": true,
        "message": "ok",
        "code": 0,
        "result": {
            "2022届": 17.20,
            "2023届": 0.00,
            "2024届": 14.10,
            "2024届全国": 18.68
        }
    }
    ```
    """
    logger.info(f"开始计算指标: 境内升学率对比分析（和全国对比  定性分析）, 项目ID: {project_id}")
    
    try:
        db = MySQLUtil()
        result_dict = {}
        
        # 辅助函数：计算指定项目的境内升学率
        def calculate_enrollment_rate(
            proj_id: int, 
            quest_ids: List[int], 
            target_year: str
        ) -> Tuple[float, str]:
            """
            计算指定项目的境内升学率
            返回: (比例值, 届次标签)
            """
            if proj_id == 0 or not quest_ids or 0 in quest_ids:
                logger.info(f"{target_year}届无数据，返回0")
                return 0.0, target_year
            
            # 查询项目配置
            proj_sql = """
            SELECT client_code, item_year, dy_target_items, split_tb_paper 
            FROM client_item 
            WHERE id = %s
            """
            proj_info = db.fetchone(proj_sql, (proj_id,))
            if not proj_info:
                logger.warning(f"未找到项目ID={proj_id}的配置信息")
                return 0.0, target_year
            
            actual_year = str(proj_info['item_year']) if proj_info['item_year'] is not None else target_year
            split_tb = proj_info['split_tb_paper']
            # 确保 split_tb 是整数类型
            split_tb = int(split_tb) if split_tb is not None else 0
            logger.info(f"项目ID={proj_id}, 届次={actual_year}, 分表后缀={split_tb}, split_tb类型={type(split_tb)}")
            
            # 查询问卷信息，筛选GRADUATE_SHORT类型
            quest_sql = f"""
            SELECT id, dy_target 
            FROM wt_template_customer 
            WHERE id IN ({','.join(['%s'] * len(quest_ids))})
            """
            quests = db.fetchall(quest_sql, tuple(quest_ids))
            valid_quest_ids = [q['id'] for q in quests if q['dy_target'] == 'GRADUATE_SHORT']
            
            if not valid_quest_ids:
                logger.warning(f"项目ID={proj_id}未找到GRADUATE_SHORT类型问卷")
                return 0.0, actual_year
            
            logger.info(f"项目ID={proj_id}找到有效问卷ID: {valid_quest_ids}")
            
            # 查询问题ID
            question_sql = """
            SELECT id, wt_code, wt_obj 
            FROM wt_template_question_customer 
            WHERE cd_template_id = %s AND wt_code = 'T00000366' AND is_del = 0
            """
            quest_info = db.fetchone(question_sql, (valid_quest_ids[0],))
            if not quest_info:
                logger.warning(f"项目ID={proj_id}未找到问题T00000366")
                return 0.0, actual_year
            
            question_id = quest_info['id']
            logger.info(f"项目ID={proj_id}找到问题ID: {question_id}")
            
            # 构建答案表名（split_tb_paper为0时不加后缀）
            # 严格比较：只有当 split_tb 明确为 0 时才不加后缀
            if split_tb == 0 or split_tb == '0':
                answer_table = "re_dy_paper_answer"
                logger.info(f"项目ID={proj_id}, split_tb={split_tb}(值为0)，使用无后缀答案表: {answer_table}")
            else:
                answer_table = f"re_dy_paper_answer_{split_tb}"
                logger.info(f"项目ID={proj_id}, split_tb={split_tb}(值为{split_tb})，使用答案表: {answer_table}")
            
            # 查询境内升学率（c2为境内升学选项）
            rate_sql = f"""
            SELECT sum(c2)/(sum(c1)+sum(c2)+sum(c3)+sum(c4)+sum(c5)+sum(c6)+sum(c7)) as rate 
            FROM {answer_table} 
            WHERE cd_template_id = %s AND wid = %s AND ans_true = 1
            """
            try:
                logger.info(f"执行SQL: {rate_sql}")
                logger.info(f"参数: ({valid_quest_ids[0]}, {question_id})")
                rate_result = db.fetchone(rate_sql, (valid_quest_ids[0], question_id))
                rate_value = float(rate_result['rate']) * 100 if rate_result and rate_result['rate'] is not None else 0.0
                
                logger.info(f"项目ID={proj_id}({actual_year}届)境内升学率: {rate_value:.2f}%")
                return rate_value, actual_year
            except Exception as e:
                logger.warning(f"项目ID={proj_id}查询答案表{answer_table}失败: {str(e)}")
                # 如果查询失败且当前使用的不是无后缀表名，尝试使用无后缀表名
                if answer_table != "re_dy_paper_answer":
                    logger.info(f"尝试使用无后缀表名 re_dy_paper_answer")
                    fallback_sql = """
                    SELECT sum(c2)/(sum(c1)+sum(c2)+sum(c3)+sum(c4)+sum(c5)+sum(c6)+sum(c7)) as rate 
                    FROM re_dy_paper_answer 
                    WHERE cd_template_id = %s AND wid = %s AND ans_true = 1
                    """
                    try:
                        rate_result = db.fetchone(fallback_sql, (valid_quest_ids[0], question_id))
                        rate_value = float(rate_result['rate']) * 100 if rate_result and rate_result['rate'] is not None else 0.0
                        logger.info(f"项目ID={proj_id}({actual_year}届)使用fallback表境内升学率: {rate_value:.2f}%")
                        return rate_value, actual_year
                    except Exception as fallback_e:
                        logger.error(f"项目ID={proj_id}fallback查询也失败: {str(fallback_e)}")
                
                logger.info(f"项目ID={proj_id}({actual_year}届)查询失败，返回0")
                return 0.0, actual_year

        # 1. 计算当前项目（如2024届）
        current_rate, current_year = calculate_enrollment_rate(
            project_id, 
            questionnaire_ids,
            "当前"
        )
        result_dict[f"{current_year}届"] = round(current_rate, 2)

        # 2. 计算上两届（如2022届）
        if (previous_year_2_project_id is not None and 
            previous_year_2_questionnaire_ids and 
            previous_year_2_project_id != 0 and 
            not (len(previous_year_2_questionnaire_ids) == 1 and previous_year_2_questionnaire_ids[0] == 0)):
            prev2_rate, prev2_year = calculate_enrollment_rate(
                previous_year_2_project_id,
                previous_year_2_questionnaire_ids,
                "往届2"
            )
            # 只有当年份是有效数字时才添加到结果中
            if prev2_year.isdigit():
                result_dict[f"{prev2_year}届"] = round(prev2_rate, 2)

        # 3. 计算上一届（如2023届）
        if (previous_year_1_project_id is not None and 
            previous_year_1_questionnaire_ids and 
            previous_year_1_project_id != 0 and 
            not (len(previous_year_1_questionnaire_ids) == 1 and previous_year_1_questionnaire_ids[0] == 0)):
            prev1_rate, prev1_year = calculate_enrollment_rate(
                previous_year_1_project_id,
                previous_year_1_questionnaire_ids,
                "往届1"
            )
            # 只有当年份是有效数字时才添加到结果中
            if prev1_year.isdigit():
                result_dict[f"{prev1_year}届"] = round(prev1_rate, 2)

        # 4. 按届次排序结果（从早到晚）
        def extract_year_for_sorting(key):
            """从键中提取年份用于排序，处理非数字情况"""
            try:
                # 移除'届'字符，提取年份
                year_str = key.replace('届', '').replace('全国', '')
                # 如果包含非数字字符（如'往1'），返回一个很大的数以排到最后
                if not year_str.isdigit():
                    return 9999
                return int(year_str)
            except:
                return 9999
        
        sorted_result = {}
        for year_key in sorted(result_dict.keys(), key=extract_year_for_sorting):
            sorted_result[year_key] = result_dict[year_key]
        result_dict = sorted_result

        # 5. 查询全国数据（使用当前项目的届次）
        national_sql = """
        SELECT sum(c2)/(sum(c1)+sum(c2)+sum(c3)+sum(c4)+sum(c5)+sum(c6)+sum(c7)) as rate
        FROM dp_bi.dy_all_paper_answer_T00000366 t1
        WHERE t1.wt_code = 'T00000366' AND t1.item_year = %s
        """
        national_rate = 0.0
        national_year = current_year
        
        try:
            logger.info(f"查询全国数据，年份: {current_year}")
            logger.info(f"执行SQL: {national_sql}")
            logger.info(f"参数: ({current_year},)")
            national_result = db.fetchone(national_sql, (current_year,))
            logger.info(f"全国数据查询结果: {national_result}")
            
            # 如果查询到数据但 rate 为 None 或 0，可能是当前年份没有全国数据，尝试前一年
            if national_result and national_result['rate'] is not None:
                national_rate = float(national_result['rate']) * 100
                logger.info(f"{current_year}届全国境内升学率: {national_rate:.2f}%")
            else:
                logger.info(f"{current_year}届全国数据为空或无效，尝试查询前一年")
                raise ValueError("当前年份无有效数据")
        except Exception as e:
            logger.warning(f"查询{current_year}年全国数据失败: {str(e)}")
            # 尝试查询前一年的全国数据
            try:
                previous_year = str(int(current_year) - 1)
                logger.info(f"尝试查询前一年全国数据，年份: {previous_year}")
                national_result = db.fetchone(national_sql, (previous_year,))
                logger.info(f"前一年全国数据查询结果: {national_result}")
                if national_result and national_result['rate'] is not None:
                    national_rate = float(national_result['rate']) * 100
                    national_year = previous_year
                    logger.info(f"{previous_year}届全国境内升学率: {national_rate:.2f}%")
                else:
                    logger.warning(f"{previous_year}年全国数据也为空")
            except Exception as e2:
                logger.warning(f"查询前一年全国数据也失败: {str(e2)}")
        
        result_dict[f"{national_year}届全国"] = round(national_rate, 2)

        logger.info(f"指标 '境内升学率对比分析（和全国对比  定性分析）' 计算成功: {result_dict}")
        return {
            "success": True,
            "message": "ok",
            "code": 0,
            "result": result_dict
        }

    except Exception as e:
        logger.error(f"计算指标 '境内升学率对比分析（和全国对比  定性分析）' 时发生错误: {str(e)}", exc_info=True)
        return {
            "success": False,
            "message": f"数据获取失败: 境内升学率对比分析（和全国对比  定性分析）",
            "code": 500,
            "error": str(e)
        }